<?php

class Category extends CI_Model {

    private $table = 'categories';
    private $courses = 'cursos';

    public function getAllRecords() {
        //$rows = $this->db->get($this->table)->result();

        
        $rows = $this->db->query("select * FROM $this->table order by name asc")->result();;
        
        return $rows;
    }
    public function getCategoriasConCursos($id_city,$publicado =1) {
        //$rows = $this->db->get($this->table)->result();
        $id_city = isset($id_city) ? $id_city : 1;
$wherepublicado = $publicado!=null ?  " and $this->courses.publicado=$publicado" : "";
        $rows = $this->db->query("select categories.*, (select count(cursos.id) from cursos
INNER JOIN institutes on institutes.id = cursos.id_instituto
 where categories.id = cursos.categoria and institutes.city_id = $id_city $wherepublicado )  as cantidad from categories  HAVING cantidad <> 0   order by name asc")->result();;
        
        return $rows;
    }  
    public function getRecordBySlug($slug,$publicado = 1) {
        
        $arrwhere = array();
        if($publicado != NULL){
        $arrwhere['publicado'] = $publicado;    
        } 
         $arrwhere["slug"] = $slug;
        $row = $this->db->select('*')->get_where($this->courses,$arrwhere)->result();
        
        return $row;
    }
    public function getCategoriaBySlug($slug) {
          $row = $this->db->query("select * FROM $this->table where slug='$slug'")->result();;
        /*$row = $this->db->select('*')->get_where($this->table, array(
                    'slug' => $slug
                ))->result();*/
          
          
          
        
        return $row;
    }
    public function getRecordById($id) {

        $row = $this->db->select('*')->get_where($this->courses, array(
                    'id' => $id
                ))->result();
        return $row;
    }
    public function getCursosByCursosSlug($slug){
          $row1 = $this->db->select('*')->get_where($this->table, array(
                    'slug' => $slug
                ))->result();    
    
        
        return  $row = $this->db->select('*')->get_where($this->courses, array(
                    'categoria' => $row1[0]->id
                ))->result(); 
    }
    function getNumCursos() {
                $cat_select = $this->uri->segment(3);

        return $this->db->query("select * FROM $this->courses where categoria = '$cat_select'")->num_rows();;
    }
    function contarCursoEnCategoria($slug_categoria,$city) {
       
      
        $filas =  $this->db->query("select $this->courses.* FROM $this->courses inner join categories on categories.id = $this->courses.categoria 
            
        INNER JOIN institutes on institutes.id = cursos.id_instituto
        where categories.slug = '$slug_categoria' and institutes.city_id = $city and cursos.publicado = 1 ")->result();
        return  count($filas);
        
    }
    function getTodosCursos($limit, $start, $order_by,$slug_categoria,$city, $recomendado = 0,$contar = false) {
       
        $this->db->limit($limit, $start);     
        
        $order_by = str_replace('.', ' ', $order_by);        
        $query = "select $this->courses.* FROM $this->courses inner join categories on categories.id = $this->courses.categoria 
            
        INNER JOIN institutes on institutes.id = cursos.id_instituto
   
        where " ;
        if($slug_categoria != null){
        $query .= "categories.slug = '$slug_categoria' and";
        }
        $query .= "  institutes.city_id = $city and cursos.publicado = 1" ;
        if($recomendado == 1){        
        $query .= " AND cursos.featured =  $recomendado" ;      
        }
        $query .=" order by featured asc , $order_by   LIMIT $start,$limit";
       
       if($contar){
           
            return $this->db->query($query)->num_rows();
           
       }  else {
       
        return $this->db->query($query)->result();
           
       }
       
        
       
        
        
    }
    function getNumFeaturedCursos($ciudad) {

        return $this->db->query("select * FROM $this->courses INNER JOIN institutes on     institutes.id = cursos.id_instituto where featured = 1 and institutes.city_id = $ciudad and cursos.publicado=1")->num_rows();;
    }
    function getProximosCursos($limit, $start,$city,$contar= false) {
         $query =    "SELECT cursos.* FROM cursos
        INNER JOIN institutes on     institutes.id = cursos.id_instituto
                WHERE institutes.city_id = $city and fecha_inicio BETWEEN CURDATE() and (CURDATE() + INTERVAL 30 DAY) ORDER BY fecha_inicio ASC ";
         
         if($contar){
             
                     return $this->db->query($query)->num_rows();
         }else{
             
            
                     return $this->db->query($query  .  " LIMIT $start,$limit ")->result();
             
         }

    }

}

?>
